package com.eorchis.module.classplanapply.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.eorchis.constants.DeptConstants;
import com.eorchis.constants.RoleConstants;
import com.eorchis.core.annotation.AuditModule;
import com.eorchis.core.basedao.dao.IDaoSupport;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.service.impl.AbstractBaseService;
import com.eorchis.core.springext.mvc.SessionConstant;
import com.eorchis.module.classplanapply.bean.ClassPlanApplyQueryBean;
import com.eorchis.module.classplanapply.bean.ClassPlanSignFormatBean;
import com.eorchis.module.classplanapply.bean.ClassPlanSignUserBean;
import com.eorchis.module.classplanapply.bean.export.ClassMinistrie;
import com.eorchis.module.classplanapply.bean.export.Ministrie;
import com.eorchis.module.classplanapply.dao.IClassPlanApplyDao;
import com.eorchis.module.classplanapply.domain.ClassPlanApply;
import com.eorchis.module.classplanapply.service.IClassPlanApplyService;
import com.eorchis.module.classplanapply.ui.commond.ClassPlanApplyQueryCommond;
import com.eorchis.module.classplanapply.ui.commond.ClassPlanApplyValidCommond;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.role.domain.Role;
import com.eorchis.module.trainingclass.domain.TrainingClass;
import com.eorchis.module.trainingclass.service.ITrainingClassService;
import com.eorchis.module.trainingclass.ui.commond.TrainingClassQueryCommond;
import com.eorchis.module.trainingclass.ui.commond.TrainingClassValidCommond;
import com.eorchis.util.excel.ExcelImportResult;
import com.eorchis.util.excel.ExcelUtil;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: ClassPlanApplyServiceImpl<br>
 * Description: 班级计划报名管理业务接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * 
 * @author SHQ
 * @createDate 2016-01-05
 * @version $Revision$
 */
// 声明本类为Server实现类，声明bean名称
@Service(value = "com.eorchis.module.classplanapply.service.impl.ClassPlanApplyServiceImpl")
@AuditModule("班级计划报名")
public class ClassPlanApplyServiceImpl extends AbstractBaseService implements
		IClassPlanApplyService {

	@Autowired
	@Qualifier("com.eorchis.module.classplanapply.dao.impl.ClassPlanApplyDaoImpl")
	private IClassPlanApplyDao classPlanApplyDao;

	public IDaoSupport getDaoSupport() {
		return classPlanApplyDao;
	}

	@SuppressWarnings("unchecked")
	public ClassPlanApplyValidCommond toCommond(IBaseEntity entity) {
		return new ClassPlanApplyValidCommond((ClassPlanApply) entity);
	}
	
	// 培训班信息接口
	@Autowired
	@Qualifier("com.eorchis.module.trainingclass.service.impl.TrainingClassServiceImpl")
	private ITrainingClassService trainingClassService;
	private List<Ministrie> resultList;

	/**
	 * @category 获取导出报名对比表
	 * @param queryCommond
	 * @return
	 * @throws Exception 
	 */
	public List<ClassPlanSignFormatBean> findExportSignComparationList(TrainingClassQueryCommond queryCommond) throws Exception{
		//1、获取计划报名列表
		List<ClassPlanSignUserBean> classPlanSignList = classPlanApplyDao.findClassPlanSignList(queryCommond);
		if(PropertyUtil.objectNotEmpty(classPlanSignList)){
			//2、按照报名单位罗列相关报名班级信息
			Map<String,List<ClassPlanSignUserBean>> signDeptMap = new HashMap<String, List<ClassPlanSignUserBean>>();
			for(String signDeptCode:DeptConstants.DEPT_NAME_ARR){
				for(ClassPlanSignUserBean bean : classPlanSignList){
					String deptCode = bean.getSignDeptCode();
					if(signDeptCode.equals(deptCode)){
						addToSignDeptMap(signDeptMap, bean);
					}
				}
			}
			//3、根据部委汇总分析班级
			List<ClassPlanSignFormatBean> resultList = new ArrayList<ClassPlanSignFormatBean>();
			for(String signDeptCode:DeptConstants.DEPT_NAME_ARR){
				List<ClassPlanSignUserBean> list = signDeptMap.get(signDeptCode);
				
				ClassPlanSignFormatBean result = new ClassPlanSignFormatBean();
				//
				if(PropertyUtil.objectNotEmpty(list)){
					List<Ministrie> classMinistrieList= addClassToMinistrieGroup(list);
					result.setMinistrieList(classMinistrieList);
				}
				result.setSignDeptCode(signDeptCode);
				result.setSignDeptName(signDeptCode);
				resultList.add(result);
			}
			return resultList;
		}
		
		return null;
	}
	//将班级信息按照部委分组
	private List<Ministrie> addClassToMinistrieGroup(List<ClassPlanSignUserBean> list){
		Map<String,List<ClassMinistrie>> resultMap = new LinkedHashMap<String,List<ClassMinistrie>>();
		Map<String,String> ministrieMap = new HashMap<String, String>();
		for(ClassPlanSignUserBean bean : list){
			String ministrieId = bean.getMinistrieId();
			String ministrieName = bean.getMinistrieName();
			ministrieMap.put(ministrieId, ministrieName);
			List<ClassMinistrie> mapList = resultMap.get(ministrieId);
			if(mapList==null){
				mapList = new ArrayList<ClassMinistrie>();
			}
			ClassMinistrie cm = new ClassMinistrie();
			cm.setClassCode(bean.getClassCode());
			cm.setClassName(bean.getClassName());
			cm.setPlanCount(bean.getPlanCount());
			cm.setAssignCount(bean.getAssignCount());
			cm.setCityPlanCount(bean.getCityPlanCount());
			cm.setCountyPlanCount(bean.getCountyPlanCount());
			cm.setAssignCityCount(bean.getAsignCityCount());
			cm.setAssignCountyCount(bean.getAsignCountyCount());
			mapList.add(cm);
			resultMap.put(ministrieId, mapList);
		}
		resultList = new ArrayList<Ministrie>();
		Set<String> keySet = resultMap.keySet();
		Iterator<String> ite = keySet.iterator();
		while(ite.hasNext()){
			Ministrie m = new Ministrie();
			String key = ite.next();
			m.setMinistrieId(key);
			m.setMinistrieName(ministrieMap.get(key));
			m.setClassMinistrieList(resultMap.get(key));
			resultList.add(m);
		}
		return resultList;
	}
	private void addToSignDeptMap(Map<String,List<ClassPlanSignUserBean>> signDeptMap,ClassPlanSignUserBean bean){
		List<ClassPlanSignUserBean> list = signDeptMap.get(bean.getSignDeptCode());
		if(list==null){
			list = new ArrayList<ClassPlanSignUserBean>();
		}
		list.add(bean);
		signDeptMap.put(bean.getSignDeptCode(), list);
	}
	/**
	 * @category 获取导入结果
	 * @param is
	 * @return
	 * @throws Exception
	 */
	public ExcelImportResult divideImportExcel(InputStream is, HttpServletRequest request) throws Exception{
		ExcelImportResult result = new ExcelImportResult();
		result.setResultSign(true);
		StringBuffer resultString = new StringBuffer("");
		Workbook wb = ExcelUtil.createWorkbook(is);
		// 只解析第一个工作表中的内容
		Sheet sheet = wb.getSheetAt(0);
		// 最大行号
		int lastRowNum = sheet.getLastRowNum();
		int errorLine = 1;

		// 获取第一行 培训班编号
		Row firstRow = sheet.getRow(0);
		// 计算培训班对应列号
		Iterator<Cell> classIterator = firstRow.cellIterator();
		while (classIterator.hasNext()) {
			Cell next = classIterator.next();
			// 列号
			int columnIndex = next.getColumnIndex();
			if (columnIndex == 0 || ((columnIndex & 1) == 0)) {
				continue;
			}
			//获取培训班ID
			String classCode = ExcelUtil.getCellValue(next);
			if(PropertyUtil.objectNotEmpty(classCode)){//班级编码不为空
//				if (!(isNumeric(classCode) && classCode.length() == 7)) {
				if (!(classCode.matches("[A-Za-z0-9]{7}") && classCode.length() <= 7)) {
					resultString.append(errorLine+++"、导入excel中第1行第"+(columnIndex+1)+"列内容错误，错误信息：培训班编号格式不正确。\r\n");
					result.setResultSign(false);
				} else {
					Role currentRole = (Role)request.getSession().getAttribute(SessionConstant.CURRENT_ROLE);
					Department currentScope = (Department) request.getSession().getAttribute(SessionConstant.CURRENT_SCOPE);
					if(RoleConstants.ROLE_CODE_BW_ADMIN.equals(currentRole.getRoleCode())){//部委管理员
						TrainingClassQueryCommond queryCommond = new TrainingClassQueryCommond();
						queryCommond.setSearchClassNum(classCode);
						queryCommond.setSearchMinistrieID(currentScope.getDeptID());
						queryCommond.setSearchActiveState(TrainingClass.IS_ACTIVE_Y);
						List<?> classList = trainingClassService.findList(queryCommond);
						if(!PropertyUtil.objectNotEmpty(classList)){
							resultString.append(errorLine+++"、导入excel中第"+(columnIndex+1)+"列编号为"+classCode+"的培训班非本部委承办，无法导入名额。\r\n");
							result.setResultSign(false);
						}
					}
				}
			} else {
				resultString.append(errorLine+++"、导入excel中第1行第"+(columnIndex+1)+"列内容错误，错误信息：培训班编号为空。\r\n");
				result.setResultSign(false);
			}
		}

		for (int i = 2; i < lastRowNum; i++) {
			Row row = sheet.getRow(i);//行号
			for (int j = 0; j < row.getLastCellNum(); j++) {
				Cell cell = row.getCell(j);
				String cellValue = ExcelUtil.getCellValue(cell);
				if(j==0){
					//报名单位简称是否正确
					boolean flag = DeptConstants.PROVINCE_SHORT_DEPNAME_MAPPING.containsValue(cellValue);
					if(!flag){
						resultString.append(errorLine+++"、导入excel中第"+(i+1)+"行第"+(j+1)+"列内容错误，错误信息：内容“"+cellValue+"”不是32个省份名称，建议模板第一列不要修改。\r\n");
						result.setResultSign(false);
					}
				}else{
					try {
						BigDecimal bd = !PropertyUtil.objectNotEmpty(cellValue.trim()) ? new BigDecimal(0) : new BigDecimal(cellValue.trim());
						if(bd.longValue()<0||bd.longValue()>1000){
							resultString.append(errorLine+++"、导入excel中第"+(i+1)+"行第"+(j+1)+"列内容错误，错误信息：内容“"+cellValue+"”必须为0~999间的正整数。\r\n");
							result.setResultSign(false);
						}
					} catch (Exception e) {
						resultString.append(errorLine+++"、导入excel中第"+(i+1)+"行第"+(j+1)+"列内容错误，错误信息：内容“"+cellValue+"”必须为0~999间的正整数。\r\n");
						result.setResultSign(false);
					}
				}
			}
		}
		result.setErrorMessage(resultString);
		return result;
	}
	
	public static boolean isNumeric(String str) {
		for (int i = str.length(); --i >= 0;) {
			if (!Character.isDigit(str.charAt(i))) {
				return false;
			}
		}
		return true;
	}
	
	public static void main(String[] args) throws Exception {
		File f = new File("D:\\work\\test\\计划名额模板.xlsx");
		InputStream is = new FileInputStream(f);
		ClassPlanApplyServiceImpl service = new ClassPlanApplyServiceImpl();
		service.importPlanApplyTb(is);
	}
	/**
	 * 导入计划报名表
	 * 
	 * @param is
	 *            输入流
	 * @return
	 * @throws Exception
	 */
	public void importPlanApplyTb(InputStream is) throws Exception {

		// 获得导入结果集
		Workbook wb = ExcelUtil.createWorkbook(is);
		// 只解析第一个工作表中的内容
		Sheet sheet = wb.getSheetAt(0);
		// 获取第一行 培训班编号
		Row firstRow = sheet.getRow(0);
		//计算培训班对应列号
		Map<Integer,String> classColMap = getTrainingClassMapByExcelRow(firstRow);
		// 最大行号
		int lastRowNum = sheet.getLastRowNum();
		List<ClassPlanApplyValidCommond> list = new ArrayList<ClassPlanApplyValidCommond>();
		for (int i = lastRowNum; i >= 1; i--) {// 循环获取32个省份所在行
			Row eachRow = sheet.getRow(i);
			Cell provinceShortNameCell = eachRow.getCell(0);
			String provinceShortName = ExcelUtil
					.getCellValue(provinceShortNameCell);
			boolean flag = DeptConstants.PROVINCE_DEPNAME_DEPID_MAPPING
					.containsKey(provinceShortName);
			if (flag) {
				List<ClassPlanApplyValidCommond> rowList = formatByRow(eachRow, provinceShortName, classColMap);
				list.addAll(rowList);
			}
			
		}
		//班级ID-对应计划报名名额
		Map<String,Integer> classPlanUserCountMap = new HashMap<String, Integer>();
		//save
		for(ClassPlanApplyValidCommond commond : list){
			//查询是否已经初始化过计划报名
			ClassPlanApplyQueryCommond queryCommond = new ClassPlanApplyQueryCommond();
			queryCommond.setSearchClassId(commond.getClassId());
			queryCommond.setSearchDeptCode(commond.getDeptCode());
			List<?> resultList = findList(queryCommond);
			if(PropertyUtil.objectNotEmpty(resultList)){
				//update
				ClassPlanApplyValidCommond c = (ClassPlanApplyValidCommond) resultList.get(0);
				c.setCityPlanCount(commond.getCityPlanCount());
				c.setCountyPlanCount(commond.getCountyPlanCount());
				c.setAsignCityCount(commond.getAsignCityCount());
				c.setAsignCountyCount(commond.getAsignCountyCount());
				c.setPlanCount(commond.getPlanCount());
				c.setAsignCount(commond.getPlanCount());
				update(c);
			}else{
				//save
				save(commond);
			}
			Integer userCount = commond.getPlanCount();
			if(classPlanUserCountMap.containsKey(commond.getClassId())){
				userCount = classPlanUserCountMap.get(commond.getClassId())+userCount;
			}
			classPlanUserCountMap.put(commond.getClassId(), userCount);
		}
		//初始化班级计划人数
		Set<String> classUserCountSet = classPlanUserCountMap.keySet();
		Iterator<String> ite = classUserCountSet.iterator();
		while(ite.hasNext()){
			String classId = ite.next();
			classPlanApplyDao.updateClassUserCountById(classId,classPlanUserCountMap.get(classId));
		}
		
	}

	//计算培训班对应列号
	private List<ClassPlanApplyValidCommond> formatByRow(Row row,String shortName,Map<Integer, String> trainingClassRowMap){
		String deptId = DeptConstants.PROVINCE_DEPNAME_DEPID_MAPPING.get(shortName);
//		String deptName = DeptConstants.PROVINCE_SHORT_DEPNAME_MAPPING.get(shortName);
		String deptName = shortName;
		Iterator<Cell> ite = row.cellIterator();
		//结果Map key=deptCode_classId
		Map<String, ClassPlanApplyValidCommond> resultMap = new LinkedHashMap<String, ClassPlanApplyValidCommond>();
		while(ite.hasNext()){
			Cell cell = ite.next();
			int columnIndex = cell.getColumnIndex();
			if(columnIndex==0){
				continue;
			}
			if(trainingClassRowMap.containsKey(columnIndex)){
				String cellValue = ExcelUtil.getCellValue(cell);
				Integer planCount = 0;
				try {
					BigDecimal bd = new BigDecimal(cellValue.trim());
					planCount = bd.intValue();
				} catch (Exception e) {
					planCount = 0;
				}
				String[] classIds = trainingClassRowMap.get(columnIndex).split("_");//classId,市 or 县
				String classId = classIds[0];
				String mapKey = shortName+"_"+classId;
				ClassPlanApplyValidCommond commond = null;
				if(resultMap.containsKey(mapKey)){
					commond = resultMap.get(mapKey);
				}else{
					commond = new ClassPlanApplyValidCommond();
				}
				commond.setDeptId(deptId);
				commond.setDeptName(deptName);
				commond.setDeptCode(shortName);
				commond.setClassId(classId);
				if("市".equals(classIds[1])){
					commond.setCityPlanCount(planCount);
					commond.setAsignCityCount(planCount);
				}
				if("县".equals(classIds[1])){
					commond.setCountyPlanCount(planCount);
					commond.setAsignCountyCount(planCount);
				}
				commond.setPlanCount(commond.getCityPlanCount()+commond.getCountyPlanCount());
				commond.setAsignCount(commond.getCityPlanCount()+commond.getCountyPlanCount());
				resultMap.put(mapKey, commond);
			}
		}
		return new ArrayList<ClassPlanApplyValidCommond>(resultMap.values());
	}
	// classId-[市，县]
	private Map<Integer, String> getTrainingClassMapByExcelRow(Row row) {

		Map<Integer, String> map = new HashMap<Integer, String>();
		// 计算培训班对应列号
		Iterator<Cell> classIterator = row.cellIterator();
		String classId = "";
		while (classIterator.hasNext()) {
			Cell next = classIterator.next();
			// 列号
			int columnIndex = next.getColumnIndex();
			if (columnIndex == 0) {
				continue;
			}
			//获取培训班ID
			String classCode = ExcelUtil.getCellValue(next);
			if(PropertyUtil.objectNotEmpty(classCode)){//班级编码不为空
				TrainingClassQueryCommond queryCommond = new TrainingClassQueryCommond();
				queryCommond.setSearchClassNum(classCode);
				List<?> classList = trainingClassService.findList(queryCommond);
				if(PropertyUtil.objectNotEmpty(classList)){
					TrainingClassValidCommond commond = (TrainingClassValidCommond) classList.get(0);
					classId = commond.getClassId();
					map.put(columnIndex, classId+"_市");
				}
			}else{
				map.put(columnIndex, classId+"_县");
			}
			
			
		}
		return map;
	}

	/**
	 * @category 查询班级的实际可报名额
	 * @param queryCommond
	 * @return
	 * @throws Exception 
	 */
	public ClassPlanApplyValidCommond findClassAsignCount(ClassPlanApplyQueryCommond queryCommond) throws Exception {
			return classPlanApplyDao.findClassAsignCount(queryCommond);
	}

	@Override
	public List<ClassPlanApplyQueryBean> findClassPlanApplyQueryBeanList(
			ClassPlanApplyQueryCommond queryCommond) throws Exception {
		return classPlanApplyDao.findClassPlanApplyQueryBeanList(queryCommond);
	}

	/**
	 * @category 查询培训班计划名额
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-29
	 */
	public List<ClassPlanApplyQueryBean> findClassPlanApplyList(ClassPlanApplyQueryCommond queryCommond) throws Exception {
		return classPlanApplyDao.findClassPlanApplyList(queryCommond);
	}
}
